"""
TODO select（算数/比较/逻辑/位运算符）

1、数据库管理系统的重要功能，即数据查询，不仅只是简单返回数据库存储的数据，还应根据需求筛选数据，并用指定格式呈现。
     用 select 来查询
2、书写顺序：
    select from
    where
    group up
    having
    order by
    limit
3、常见操作
    select s.id from student as s;
    select distinct name from student;
    select age+10 from student;
    select p.id, u.id from produce p ,user u;
4、运算符
   ① 算术运算符：+   -   *   /或 DIV    %或 MOD
         select age * 2, id from student;

   ② 比较运算符：=   <、<=    >、>=
               <=>                                （安全的等于，两个操作码均为NULL，其所得值为1，一个操作码为NULL，其所得值为0）
               <>或!=
               is null 或 isnull     is not null
               least                               (返回最小值)
               greatest                           （返回最大值）
               between and                        （判断是否在两值之间）
               in    not in                       （是否为IN列表的任一值）
               like                               （通配符匹配）
               regexp                             （正则表达式匹配）
                    年龄不等于10的：     select * from student where         not(age = 10);
                    年龄在10和20之间的： select * from student where         age between 10 and 20;
                                      select * from student where         age> 10 and(或&&) age < 20;
                     年龄是10或20的：   select * from student where         age in(10,20);
                                      select * from student where         age = 10 or(或||) age =20;
                    含有’鞋‘字的：       select * from student where         name like '% 鞋 %';
                    以'海'开头的：       select * from student where         name like '海 %';
                    第二个字为'寇'的：    select * from student where         name like '_寇 %';
                    age为null的：       select * from student where         age  is null;
                    age不为null的：      select * from student where         age is not null;
                    age的最小值：        select min(age) from student;
                                       select least(19,2,3) as small_number; -- 如果其中包含null，不进行比较，结果直接返回null
                    age的最大值：        select max(age) from student;
                                       select greatest(19,2,3) as big_number;

    ③ 逻辑运算符：not 或 ！      （逻辑非）
                and 或 &&      （逻辑与）
                or  或 ||      （逻辑或）
                xor            （逻辑异或）   -- 不同为真，相同为假

     ④ 位运算符：|      (按位或)
                &      (按位与)
                ^      (按位异或)  -- 相同为0，不同为1
                <<     (按位左移)
                >>     (按位右移)
                ~      (按位取反，反转所有比特)
        注：位运算符是在二进制数上计算，先将操作数变成二进制数，进行位运算，在将结果由二进制数转变成十进制数

select 3 & 5;     3 | 5      3 ^ 5     3 << 5            3 >> 5              3 ~ 5
0011              0011       0011      0011 -> 0001      0011 -> 0110        0011
0101              0101       0101
-----             -----      -----
0001              0111       0110

TODO 排序查询

    在降序价格的基础上，升序分类：   select * from product order by price desc, category asc;
    选择价格去重，并降序：         select distinct price  from product order by price desc;

TODO 聚合查询

注：会忽略null
  商品的总条数（一共记录了多少行）： select count(*) from product;
  价格大于200的商品总条数：       select  count(*) from product where price > 200;
  分类为’c001‘的总价，平均价格：  select  sum(price) sum_price,avg(price) avg_price from product where category = 'c001';
  商品的最大和最小价格：          select  max(price) max_price，min(price) min_price from product ;
null值的处理：
    1、count()：为*，所有记录的个数
               某字段，不含null
    2、sum()、avg()：忽略null，计数也不包含null所在的行
                    可以处理为：c1 int default 0，
    3、max()、min()：忽略null


TODO 分组查询

注：select后只能跟分组字段和聚合函数
   分组之后，筛选统计结果必须用having，不能用where
     统计各个分类的商品数：          select category, count(*) from product group by category;
     统计不同地区的学生个数：        select ......  from student group by  省，市，县；
区别：
  where：   筛选from子句指定的操作所产生的行
  having：  分组where子句的输出
  group by：从分组的结果中筛选行

顺序：
   from
   where
   group by
   having
   order by
   limit

   统计各个分类的商品个数，只显示个数大于4的：     select category, count(*) from product group by category having count(*) > 4;

TODO 分页查询

项目开发中常用，数据量大，显示屏长度有限，采取分页显示方式 (京东看商品，设置了每页可以浏览多少个商品)
     显示前5条记录：           select * from product limit 5;
     从第4条开始，显示5条：     select * from product limit 3, 5;   -- limit m(当前页-1), n   第一行为0行
     分页显示：
         第一页：     select * from product limit 0,60; -- 实际是0-59那几条
         第二页：     select * from product limit 60,60;
         第三页：     select * from product limit 120,60;

TODO insert into select语句

将一张表的数据导入另一张表
注：要求目标表2必须存在
     product1(age,name,gender)
     product2(age,name)
     product3(category,count)

     insert into product2(age,name) select(age,name) from product1;
     insert into product3 select category,count(*) from product1 group by category;
综合：
按照性别排序，计算性别个数，且保留个数大于4的：
     select gender,count(*) as total_c from student group by gender having total_c >4 order by total_c desc;
姓名第二个字母不为A，且薪资大于1000，并按照年薪降序排序（年薪=月薪*12+奖金，奖金列有null）：
     select * from emp where name not like'_A%' and sal >1000 order by (sal*12 +ifnull(comm,0)) desc;
求每个部门的平均薪水，按照平均薪水降序排序：
     select deptno,avg(sal) from emp group by deptno order by avg(sal) desc;
求每个部门每个岗位的最高薪水：
     select deptno,job,max(sal) from emp group by deptno, job;
求平均薪水大于2000的部门编号：
     select avg(sal),deptno from emp group by deptno having avg(sal)>2000;
选择有奖金的员工姓名、工资：
     select ename,sal from emp where comm is not Null;

TODO 正则表达式

1、概念：regular expression，正则表达式本身是一个字符串，描述、定义匹配规则，通常用来检索、替换那么符合某个规则的文本
2、格式：
    ^(a|b)：    以a或b开头
    a$：        a为字符串的结束
    a.：        a后有字符串，除/n外的任一单个字符
    .a：        a前有字符串，除/n外的任一单个字符
    [abc]：     匹配a、b、c中的任一字符
    [^abc]：    除abc外的其他字符
    z|food：    匹配z、food
    (z|f)ood：  zood、food
    a*：        a出现零次或多次，包括空字符串
    a+：        a出现一次或多次
    a?：        a出现0次或1次
    a{2}：      a刚好出现2次
    a{2,}:     a的次数在[2,+∞）
    {3，5}：    a的次数在[3,5]
3、操作：
    select 'abc' regexp '^a';           1（1为真，表示匹配成功）
        select * from product where pname REGEXP '^海';
    select 'abc' regexp 'a$';           0
    select 'abc' regexp 'c$';           1
        select * from product where pname REGEXP '水$';
    select 'abc' regexp '.b';           1     b前有a
    select 'abc' regexp '.c';           1     c前有b
    select 'abc' regexp 'a.';           1     a后有b
    select 'abc' regexp '[xyz]';        0     xyz没有字符在abc中出现
    select 'abc' regexp '[xaz]';        1     xaz有a在abc中出现
    select 'a' regexp '[^abc]';        0     前面的a出现在abc中
    select 'x' regexp '[^abc]';        1     前面的x没有出现在abc中
    select 'abc' regexp '[^a]';        1     字符串abc中，b不是a
    select 'stab' regexp '.ta*b';      1    .t -- t前有s，符合；    a* --  a出现0次或多次，stab中a出现1次，满足
    select 'stb' regexp '.ta*b';       1    .t -- t前有s，符合；    a* --  a出现0次或多次，stab中a出现0次，满足
    select '' regexp 'a*';             1     a出现0次或多次，包含空字符串
    select 'stab' regexp '.ta+b';       1   .t -- t前有s，符合；    a+ --  a出现1次或多次，stab中a出现1次，满足
    select 'stb' regexp '.ta+b';        0   .t -- t前有s，符合；    a+ --  a出现1次或多次，stab中a出现0次，不满足
    select 'stb' regexp '.ta?b';        1   .t -- t前有s，符合；    a? --  a出现0次或1次，stb中a出现0次，满足
    select 'stab' regexp '.ta?b';       1   .t -- t前有s，符合；    a? --  a出现0次或1次，stab中a出现1次，满足
    select 'staab' regexp '.ta?b';      0   .t -- t前有s，符合；    a? --  a出现0次或1次，staab中a出现2次，不满足
    select 'a' regexp 'a|b';            1    出现a或b，a满足
    select 'b' regexp 'a|b';            1    出现a或b，b满足
    select 'a' regexp '^(a|b)';         1    以a或b开头，a满足
    select 'b' regexp '^(a|b)';         1    以a或b开头，b满足
    select 'c' regexp '^(a|b)';         0    以a或b开头，c不满足

    select name,
        case when continent in ( 'Europe','Asia') then 'Eurasia'
             when continent in ( 'North America','South America','Caribbean') then 'America'
        else
            continent
        end
    from world where name  regexp '^(A|B)';

    select 'auuuuc' regexp 'au{4}c';    1    有4个u
    select 'auuuuc' regexp 'au{3}c';    0    3个u,不对

    select 'auuuuc' regexp 'au{3,}c';   1    u的次数在[3,+∞），满足
    select 'auuuuc' regexp 'au{4,}c';   1    u的次数在[4,+∞），满足
    select 'auuuuc' regexp 'au{5,}c';   0    u的次数在[5,+∞），不满足

    select 'auuuuc' regexp 'au{3,5}c';    1   u的次数在[3,5]之间
    select 'auuuuc' regexp 'au{4,5}c';    1   u的次数在[4,5]之间
    select 'auuuuc' regexp 'au{5,10}c';   0   u的次数在[5,10]之间

    select 'xababy' regexp 'x(abab)y';     1
    select 'xababy' regexp 'x(ab)*y';      1
    select 'xababy' regexp 'x(ab){1,2}y';  1
    select 'xababy' regexp 'x(ab){3}y';    0
"""